package cs5226.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;

import cs5226.model.SQLResultSet;

public class BaselineDAO {
	public static ArrayList<String> getBaselines() throws SQLException {
		String query = "select baseline_name from DBA_HIST_BASELINE order by baseline_name";
		ArrayList<String> list = new ArrayList<String>();
		
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, 
				ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = stmt.executeQuery();
		
		/**
		 * 
		 */
		while (rs.next())
		{
			list.add(rs.getString("baseline_name"));
		}
		
		DBUtil.close(rs, stmt, conn);
		
		/*
		while (rs.next()) {
			System.out.println (rs.getString(1));
		}
		*/
		
		return list;
	}
	
	/**
	 * Get all the metrics, based on a specific snapshot - not needed
	 * @param snap_id
	 * @return
	 */
	public static ArrayList<String> getAllMetrics(int snap_id) throws SQLException
	{
		//get all metrics from snapshot table
		String query = "select distinct metric_id,metric_name,metric_unit " +
				" from dba_hist_sysmetric_summary where snap_id = "+snap_id;
		
		ArrayList<String> list = new ArrayList<String>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {			
			stmt = conn.prepareStatement(query, ResultSet.TYPE_SCROLL_INSENSITIVE, 
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery();
			
			while (rs.next())
			{
				list.add(rs.getString("metric_name")+"("+rs.getString("metric_unit")+")");
			}
		}
		finally
		{
			DBUtil.close(rs, stmt, conn);
		}
		
		return list;
	}
	
	//Limit to maximum 3 baselines only
	public static LinkedHashMap getBaselinesNSnapshot(String[] baselineNameList) throws Exception
	{
		//get baseline's metric values
		String sql = "select metric_name,metric_unit,average,minimum,maximum from table " +
		" (DBMS_WORKLOAD_REPOSITORY.select_baseline_metric(?)) " +
		" order by metric_name ";
		
		int max = baselineNameList.length;
		if (max > 3)
			max = 3;
		
		ArrayList<String> bl1 = new ArrayList<String>();
		ArrayList<String> bl2 = new ArrayList<String>();
		ArrayList<String> bl3 = new ArrayList<String>();
		ArrayList<String> snp = new ArrayList<String>();
		ArrayList<String> mt = new ArrayList<String>();
		
		Connection conn = DBUtil.getConnection();
		PreparedStatement stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, 
				ResultSet.CONCUR_READ_ONLY);
		ResultSet rs = null;
		
		DecimalFormat df=new DecimalFormat("0.00000");
		
		for (int i=0;i<max;i++)
		{
			String baselineName = baselineNameList[i];			
			stmt.setString(1, baselineName);
			rs = stmt.executeQuery();
			
			if (i==0)
			{
				while (rs.next()){
					bl1.add("<td>" + df.format(rs.getDouble("average")) + "</td>");
				}
			}
			else if (i==1)
			{
				while (rs.next()){
					bl2.add("<td>" + df.format(rs.getDouble("average")) + "</td>");
				}
			}
			else if (i==2)
			{
				while (rs.next()){
					bl3.add("<td>" + df.format(rs.getDouble("average")) + "</td>");
				}
			}
		}
		
		//get latest snapshot metric values
		sql = "select metric_name,average from dba_hist_sysmetric_summary where snap_id = " +
				" (select max(snap_id) from dba_hist_sysmetric_summary) order by metric_name";
		stmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, 
				ResultSet.CONCUR_READ_ONLY);
		rs = stmt.executeQuery();
		
		while (rs.next())
		{
			snp.add("<td>" + df.format(rs.getDouble("average")) + "</td>");
			mt.add("<td>" + rs.getString("metric_name") + "</td>");
		}
		
		//close connection
		DBUtil.close(rs, stmt, conn);
		rs = null; stmt = null; conn = null;
		
		//Arrange data into LinkedHashMap
		int count = 0;
		if (bl1 != null && bl1.size() > 0)
			count++;
		if (bl2 != null && bl2.size() > 0)
			count++;
		if (bl3 != null && bl3.size() > 0)
			count++;
		
		if (count == 0)
			return null;
		
		LinkedHashMap<String, ArrayList<String>> Lhm = new LinkedHashMap<String, ArrayList<String>>();
		
		if (baselineNameList.length>0)
			bl1.add(0, "<td class='TableHeader'><b>"+baselineNameList[0]+"</b></td>");
		if (baselineNameList.length>1)
			bl2.add(0, "<td class='TableHeader'><b>"+baselineNameList[1]+"</b></td>");
		if (baselineNameList.length>2)
			bl3.add(0, "<td class='TableHeader'><b>"+baselineNameList[2]+"</b></td>");
		mt.add(0,"<td class='TableHeader'><b>Metric Name</b></td>");
		snp.add(0,"<td class='TableHeader'><b>Latest Snapshot</b></td>");
		
		for (int k = 0;k<mt.size();k++)
		{
			ArrayList<String> row = new ArrayList<String>();
			for (int l = 0;l<count;l++)
			{
				if (l == 0)
					row.add(bl1.get(k));
				else if (l == 1)
					row.add(bl2.get(k));
				else if (l == 2)
					row.add(bl3.get(k));
			}
			row.add(snp.get(k));
			
			Lhm.put(mt.get(k), row);
		}
		
		return Lhm;
	}
	
}
